package com.decent.ejfadmin.finance.dao;

import com.decent.ejfadmin.common.bean.*;
import com.decent.ejfadmin.finance.dao.provider.SupplyDailySheetDaoProvider;
import com.decent.ejfadmin.finance.response.dto.SupplyDailySheetDto;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 供货系统日报表管理
 *
 * @author 王元鑫
 * @date 2016/8/6
 */
@Mapper
@Repository("SupplyDailySheetDao")
public interface SupplyDailySheetDao {
    /**
     * 查询日报表的用户信息
     *
     * @param time
     * @return
     */
    @Select("select t.userId from  yuhuang_supply_daily_sheet t where t.startTime=#{time}")
    List<Long> querySupplyDailySheetUserId(@Param("time") String time);

    /**
     * 统计消费过的用户
     *
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("select DISTINCT t.userId  from yuhuang_user_consumption t where  t.type = 0 and t.startTime>=#{startTime} \n" +
            "and t.startTime<#{endTime} ")
    List<Long> queryUserConsumption(@Param("startTime") String startTime, @Param("endTime") String endTime);


    /**
     * 查询会员消费明细
     *
     * @param userId    会员id
     * @param startTime 查询开始时间
     * @param sort      排序
     * @return list
     */
    @SelectProvider(type = SupplyDailySheetDaoProvider.class, method = "queryUserSupplyConsumption")
    SupplyConsumption queryUserSupplyConsumption(@Param("userId") Long userId, @Param("startTime") String startTime, @Param("sort") String sort);

    /**
     * 保存每日期初余额
     *
     * @param beginBalance 期初余额
     * @param time         日期
     */
    @Insert("insert into yuhuang_supply_daily_balance (userId,startTime,balance)" +
            " values (#{beginBalance.userId},#{time},#{beginBalance.balance})" +
            " ON DUPLICATE KEY UPDATE balance=#{beginBalance.balance}")
    void saveDailyBalance(@Param("beginBalance") SupplyBalance beginBalance, @Param("time") String time);

    /**
     * 查询期初余额
     *
     * @param userId 会员id
     * @param time   日期
     * @return 期初余额
     */
    @Select("select balance as beginBalance from yuhuang_supply_daily_balance where userId = #{userId} and startTime = #{time}")
    SupplyDailySheet queryBeginBalance(@Param("userId") Long userId, @Param("time") String time);

    /**
     * 计算提现金额（支付宝）
     *
     * @param userId 会员id
     * @param time   日期
     * @return 提现金额
     */
    @Select("select sum(price) as withdrawMoney from yuhuang_withdraw_record where userId = #{userId} " +
            "and endTime >= #{time} and endTime < DATE_ADD(#{time},INTERVAL 1 DAY) and state=2 and instr(withdrawChannel,'alipay') > 0")
    SupplyDailySheet queryWithdrawMoney(@Param("userId") Long userId, @Param("time") String time);

    /**
     * 计算提现金额（银行）
     *
     * @param userId 会员id
     * @param time   日期
     * @return 提现金额
     */
    @Select("select sum(price) as withdrawBankMoney,sum(servicePrice) servicePrice from yuhuang_withdraw_record where userId = #{userId} " +
            "and endTime >= #{time} and endTime < DATE_ADD(#{time},INTERVAL 1 DAY) and state=2 and instr(withdrawChannel,'bank') > 0")
    SupplyDailySheet queryWithdrawBankMoney(@Param("userId") Long userId, @Param("time") String time);

    /**
     * 计算补结金额(三网+虚商)
     *
     * @param userId 会员id
     * @param time   日期
     * @return 三网+虚商补结金额
     */
    @Select("SELECT sum(settlePrice * userPrice) AS fixMoney FROM yuhuang_supply_card WHERE endTime >= " +
            " #{time} AND endTime <  DATE_ADD(#{time},INTERVAL 1 DAY) AND " +
            " userid = #{userId} AND status = 1 AND added = 1 AND productType < 300")
    SupplyDailySheet queryFixMoney(@Param("userId") Long userId, @Param("time") String time);

    /**
     * 计算补结金额(加油卡)
     *
     * @param userId 会员id
     * @param time   日期
     * @return 加油卡补结金额
     */
    @Select("SELECT sum(settlePrice * userPrice) AS fixFuelMoney FROM yuhuang_supply_card WHERE endTime >= " +
            " #{time} AND endTime < DATE_ADD(#{time},INTERVAL 1 DAY) AND " +
            " userid = #{userId} AND status = 1 AND added = 1 AND productType >= 300 AND productType <= 310")
    SupplyDailySheet queryFixFuelMoney(@Param("userId") Long userId, @Param("time") String time);

    /**
     * 计算补结金额(权益)
     *
     * @param userId 会员id
     * @param time   日期
     * @return 权益补结金额
     */
    @Select("SELECT sum(settlePrice) AS fixEquityMoney FROM yuhuang_supply_card WHERE endTime >= " +
            " #{time} AND endTime < DATE_ADD(#{time},INTERVAL 1 DAY) AND " +
            " userid = #{userId} AND status = 1 AND added = 1 AND productType > 310")
    SupplyDailySheet queryFixEquityMoney(@Param("userId") Long userId, @Param("time") String time);

    /**
     * 计算补扣款(权益)
     *
     * @param userId 会员id
     * @param time   日期
     * @return 权益补结金额
     */
    @Select("SELECT sum(settlePrice) AS fixEquityMoney FROM yuhuang_supply_card WHERE endTime >= " +
            " #{time} AND endTime < DATE_ADD(#{time},INTERVAL 1 DAY) AND " +
            " userid = #{userId} AND status = 1 AND added = 2 AND productType > 310")
    SupplyDailySheet queryFixDeductEquityMoney(@Param("userId") Long userId, @Param("time") String time);

    /**
     * 计算运营商成功面值、入账金额
     *
     * @param userId 会员id
     * @param time   日期
     * @return list
     */
    @Select("SELECT\n" +
            "  case when productType >310 then 1100 else productType end as productTypeFlag,\n" +
            "  sum(settlePrice)             AS successFace,\n" +
            "  sum(settlePrice * userPrice) AS entryMoney,\n" +
            "  sum(userPrice)             AS equityMoney,\n" +
            "  sum(face)             AS equitySuccessFace\n" +
            "FROM yuhuang_supply_card\n" +
            "WHERE endTime >= #{time} AND endTime < " +
            " DATE_ADD(#{time},INTERVAL 1 DAY) AND userid = #{userId} AND status = 1 AND added = 0\n" +
            "GROUP BY productTypeFlag")
    List<Map<String, Object>> querySummary(@Param("userId") Long userId, @Param("time") String time);

    /**
     * 计算不同订单来源汇总金额
     *
     * @param userId 会员id
     * @param time   日期
     * @return list
     */
    @Select("SELECT " +
            "  t.agentNo, " +
            "  SUM(entryMoney) AS entryMoney " +
            "FROM " +
            "  ( " +
            "    SELECT " +
            "      CASE " +
            "    WHEN agentNo IN (13950, 14165) THEN " +
            "      'EJF' " +
            "    WHEN agentNo IN (13630, 14350,18370) THEN " +
            "      'EASY' " +
            "    END agentNo, " +
            "    sum(settlePrice * userPrice) AS entryMoney " +
            "  FROM " +
            "    yuhuang_supply_card " +
            "  WHERE " +
            "    endTime >= #{time} " +
            "  AND endTime < DATE_ADD( " +
            "   #{time}, " +
            "    INTERVAL 1 DAY " +
            "  ) " +
            "  AND userid = #{userId} " +
            "  AND STATUS = 1 " +
            "  AND agentNo IS NOT NULL " +
            "  GROUP BY " +
            "    agentNo " +
            "  ) t " +
            "GROUP BY " +
            "  t.agentNo")
    List<SupplyDailySheet> querySummaryByAgentNo(@Param("userId") Long userId, @Param("time") String time);

    /**
     * 计算工号成功面值、入账金额
     *
     * @param userId 会员id
     * @param time   日期
     * @return list
     */
    @Select("SELECT\n" +
            "  sum(wod.money)           AS successFace,\n" +
            "  sum(wod.money * wo.cost) AS entryMoney\n" +
            "FROM yuhuang_worknumber_order_detail wod LEFT JOIN yuhuang_worknumber_order wo ON wod.orderId = wo.orderId\n" +
            "WHERE wod.dealTime >= #{time} AND wod.dealTime < DATE_ADD(#{time},INTERVAL 1 DAY) and wod.state = 1 AND wo.userId = #{userId}")
    Map<String, Object> queryWorkNumberSummary(@Param("userId") Long userId, @Param("time") String time);

    /**
     * 计算Q币成功面值、入账金额
     *
     * @param userId 会员id
     * @param time   日期
     * @return list
     */
    @Select("SELECT SUM(qp.face*qo.purchase_amount) successFace ,SUM(qp.face*qo.purchase_amount*qo.price) entryMoney " +
            "FROM `yuhuang_qb_order`qo,yuhuang_qb_product qp WHERE qo.product_id=qp.id " +
            "and qo.end_time >= #{time} AND qo.end_time < DATE_ADD(#{time},INTERVAL 1 DAY) and qo.status = 1 AND qo.user_id = #{userId}")
    Map<String, Object> queryQbOrderSummary(@Param("userId") Long userId, @Param("time") String time);

    /**
     * 日报表存库
     *
     * @param sheet 日报表
     */
    @InsertProvider(type = SupplyDailySheetDaoProvider.class, method = "createDailySheet")
    void createDailySheet(@Param("sheet") SupplyDailySheet sheet);

    /**
     * 计算返佣+手动补结算+补差额金额+补手续费
     *
     * @param id   会员id
     * @param time 查询开始时间
     * @return 返佣+手动补结算+补差额金额+补手续费
     */
    @Select("select sum(uc.money) rebate from yuhuang_user_consumption uc where uc.userId = #{userId}  and uc.type = 0 and " +
            "uc.startTime >=  #{startTime} and uc.startTime < DATE_ADD(#{startTime},INTERVAL 1 DAY) " +
            " and consumeType in (6,7,8,11)  ")
    SupplyDailySheet queryRebate(@Param("userId") Long id, @Param("startTime") String time);

    /**
     * 查询手动退款
     *
     * @param id   会员id
     * @param time 查询开始时间
     * @return 返佣+手动补结算+补差额金额+补手续费
     */
    @Select("select sum(uc.money) manualRefund from yuhuang_user_consumption uc where uc.userId = #{userId}  and uc.type = 0 and " +
            "uc.startTime >=  #{startTime} and uc.startTime < DATE_ADD(#{startTime},INTERVAL 1 DAY) " +
            " and consumeType=12  ")
    SupplyDailySheet queryManualRefund(@Param("userId") Long id, @Param("startTime") String time);

    /**
     * 分页查询供货日报表计数
     *
     * @param startTime   查询开始时间
     * @param endTime     查询结束时间
     * @param userAccount 会员编号
     * @return 总数
     */
    @SelectProvider(type = SupplyDailySheetDaoProvider.class, method = "querySupplyDailySheetCount")
    int querySupplyDailySheetCount(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("userAccount") String userAccount);

    /**
     * 分页查询供货日报表
     *
     * @param pageUnit    分页组件
     * @param startTime   查询开始时间
     * @param endTime     查询结束时间
     * @param userAccount 会员编号
     * @return list
     */
    @SelectProvider(type = SupplyDailySheetDaoProvider.class, method = "querySupplyDailySheetPage")
    List<SupplyDailySheetDto> querySupplyDailySheetPage(@Param("page") Page pageUnit, @Param("startTime") String startTime, @Param("endTime") String endTime, @Param("userAccount") String userAccount);
}
